package com.main.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.main.pojo.TrollJoke;
import com.main.pojo.TrollPhoto;
import com.main.pojo.Uploader;
import com.main.utils.Constant;
import com.main.utils.HibernateUtils;

@Transactional
@Repository
public class TrollDAOImpl implements TrollDAO {

	private SessionFactory sessionFactory;

	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<TrollPhoto> getTrollPhoto(int pageNumber) throws Exception {
		try {
			String hql = "SELECT t.id, t.title, t.viewNumber, t.commentNumber, t.uploader.id, t.uploader.name, " +
					"t.photoType, t.url, t.sexy FROM TrollPhoto t WHERE t.status = 'A' ORDER BY t.id DESC";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setFirstResult((pageNumber - 1) * Constant.PHOTO_PAGE_SIZE);
			query.setMaxResults(Constant.PHOTO_PAGE_SIZE);
			List<TrollPhoto> results = new ArrayList<TrollPhoto>();
			List<Object[]> ds = query.list();
			if(ds != null){
				for (Object[] obj : ds) {
					TrollPhoto troll = new TrollPhoto();
					Uploader uploader = new Uploader();
					troll.setId((Integer)obj[0]);
					troll.setTitle((String)obj[1]);
					troll.setViewNumber((Integer)obj[2]);
					troll.setCommentNumber((Integer)obj[3]);
					uploader.setId((Integer)obj[4]);
					uploader.setName((String)obj[5]);
					troll.setUploader(uploader);
					troll.setPhotoType((String)obj[6]);
					troll.setUrl((String)obj[7]);
					troll.setSexy((String)obj[8]);
					results.add(troll);
				}
			}
			if (results.size() > 0) {
				List<Integer> trollsId = new ArrayList<Integer>();
				for (TrollPhoto troll : results) {
					trollsId.add(troll.getId());
				}
				updateViewsNumber(trollsId);
			}
			return results;
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public TrollPhoto getTrollPhotoById(int id) throws Exception {
		TrollPhoto troll = null;
		try {
			troll = (TrollPhoto) sessionFactory.getCurrentSession().get(TrollPhoto.class, id);
			troll.setPrevTrollId(getPrevTroll(id));
			troll.setNextTrollId(getNextTroll(id));
			if (troll != null) {
				updateViewsNumber(troll.getId());
			}
		} catch (Exception e) {
			throw e;
		}
		return troll;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<TrollJoke> getTrollJoke(int pageNumber) throws Exception {
		try {
			String hql = "Select t.id, t.title, t.viewNumber, t.commentNumber, t.headerContent, t.image," +
					"t.uploader.id, t.uploader.name FROM TrollJoke t WHERE t.status = 'A' ORDER BY t.id DESC";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setFirstResult((pageNumber - 1) * Constant.JOKE_PAGE_SIZE);
			query.setMaxResults(Constant.JOKE_PAGE_SIZE);
			List<TrollJoke> results = new ArrayList<TrollJoke>();
			List<Object[]> ds =  query.list();
			if(ds != null){
				for (Object[] obj : ds) {
					TrollJoke troll = new TrollJoke();
					Uploader uploader = new Uploader();
					troll.setId((Integer)obj[0]);
					troll.setTitle((String)obj[1]);
					troll.setViewNumber((Integer)obj[2]);
					troll.setCommentNumber((Integer)obj[3]);
					troll.setHeaderContent((String)obj[4]);
					troll.setImage((String)obj[5]);
					uploader.setId((Integer)obj[6]);
					uploader.setName((String)obj[7]);
					troll.setUploader(uploader);
					results.add(troll);
				}
			}
			return results;
		} catch (Exception e) {
			throw e;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<TrollJoke> getJokeRelative(int pageNumber) throws Exception {
		try {
			String hql = "Select t.id, t.title, t.image FROM TrollJoke t WHERE t.status = 'A' ORDER BY t.id DESC";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setFirstResult((pageNumber - 1) * 5);
			query.setMaxResults(5);
			List<TrollJoke> results = new ArrayList<TrollJoke>();
			List<Object[]> ds =  query.list();
			if(ds != null){
				for (Object[] obj : ds) {
					TrollJoke troll = new TrollJoke();
					troll.setId((Integer)obj[0]);
					troll.setTitle((String)obj[1]);
					troll.setImage((String)obj[2]);
					results.add(troll);
				}
			}
			return results;
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public int addTrollPhoto(TrollPhoto troll) throws HibernateException, SQLException {
		try {
			HibernateUtils.IdentityInsertOn(sessionFactory, "TROLL");
			Integer id = (Integer) sessionFactory.getCurrentSession().save(troll);
			return id;
		} catch (HibernateException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		}
	}

	@Override
	public int getNextTroll(int curentTrollId) throws Exception {
		int result = -1;
		try {
			Query query = sessionFactory.getCurrentSession().createQuery(
					"select MAX(t.id) from TrollPhoto t where t.id < :id and t.status = :status");
			query.setParameter("id", curentTrollId);
			query.setParameter("status", Constant.APPROVED);
			Object obj = query.uniqueResult();
			if (obj != null) {
				result = (Integer) obj;
			}
		} catch (Exception e) {
			throw e;
		}
		return result;
	}

	@Override
	public int getPrevTroll(int curentTrollId) throws Exception {
		int result = -1;
		try {
			Query query = sessionFactory.getCurrentSession().createQuery(
					"select MIN(t.id) from TrollPhoto t where t.id > :id and t.status = :status");
			query.setParameter("id", curentTrollId);
			query.setParameter("status", Constant.APPROVED);
			Object obj = query.uniqueResult();
			if (obj != null) {
				result = (Integer) obj;
			}
		} catch (Exception e) {
			throw e;
		}
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<TrollPhoto> getPhotoMostView() throws Exception {
		try {
			String hql = "SELECT t.id, t.title, t.viewNumber, t.commentNumber, t.photoType, t.url, t.sexy FROM TrollPhoto t WHERE t.status = 'A' and t.postDate >= :postDate ORDER BY t.viewNumber DESC, t.postDate DESC";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			Calendar cal = Calendar.getInstance();
			cal.add(Calendar.DATE, -3); // get most viewed today and yesterday.
			query.setDate("postDate", cal.getTime());
			query.setFirstResult(0);
			query.setMaxResults(Constant.MOST_VIEWED_SIZE);
			List<TrollPhoto> results = new ArrayList<TrollPhoto>();
			List<Object[]> ds = query.list();
			if(ds != null){
				for (Object[] obj : ds) {
					TrollPhoto troll = new TrollPhoto();
					troll.setId((Integer)obj[0]);
					troll.setTitle((String)obj[1]);
					troll.setViewNumber((Integer)obj[2]);
					troll.setCommentNumber((Integer)obj[3]);
					troll.setPhotoType((String)obj[4]);
					troll.setUrl((String)obj[5]);
					troll.setSexy((String)obj[6]);
					results.add(troll);
				}
			}
			return results;
		} catch (Exception e) {
			throw e;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<TrollJoke> getJokeMostView() throws Exception {
		try {
			String hql = "Select t.id, t.title, t.viewNumber, t.commentNumber, t.image FROM TrollJoke t WHERE t.status = 'A' and t.postDate >= :postDate ORDER BY t.viewNumber DESC, t.postDate DESC";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			Calendar cal = Calendar.getInstance();
			cal.add(Calendar.DATE, -3); // get most viewed today and yesterday.
			query.setDate("postDate", cal.getTime());
			query.setFirstResult(0);
			query.setMaxResults(Constant.MOST_VIEWED_SIZE);
			List<TrollJoke> results = new ArrayList<TrollJoke>();
			List<Object[]> ds =  query.list();
			if(ds != null){
				for (Object[] obj : ds) {
					TrollJoke troll = new TrollJoke();
					troll.setId((Integer)obj[0]);
					troll.setTitle((String)obj[1]);
					troll.setViewNumber((Integer)obj[2]);
					troll.setCommentNumber((Integer)obj[3]);
					troll.setImage((String)obj[4]);
					results.add(troll);
				}
			}
			return results;
		} catch (Exception e) {
			throw e;
		}
	}

	private void updateViewsNumber(List<Integer> trollsId) {
		String ids = "";
		if (trollsId == null || trollsId.size() == 0) {
			return;
		}
		for (Integer id : trollsId) {
			if (!ids.isEmpty()) {
				ids += ",";
			}
			ids += id;
		}
		String hql = "UPDATE Troll t set t.viewNumber = (t.viewNumber + 1) WHERE t.id in (" + ids + ")";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.executeUpdate();

	}

	private void updateViewsNumber(int trollId) throws Exception {
		try {
			String hql = "UPDATE Troll t set t.viewNumber = (t.viewNumber + 1) WHERE t.id = " + trollId;
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.executeUpdate();
		} catch (Exception e) {
			throw e;
		}

	}

	@Override
	public void updateLike(int trollId) throws Exception {
		try {
			String hql = "UPDATE Uploader u set u.likeNumber = (u.likeNumber+1) "
					+ "WHERE u.id = (Select t.uploader.id From Troll t Where t.id = " + trollId + " )";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.executeUpdate();
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public void updateComment(int trollId) throws Exception {
		try {
			String hql = "UPDATE Uploader u set u.commentNumber = (u.commentNumber+1) "
					+ "WHERE u.id = (Select t.uploader.id From Troll t Where t.id = " + trollId + " )";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.executeUpdate();

			hql = "UPDATE Troll t SET t.commentNumber = (t.commentNumber + 1) Where t.id = " + trollId;
			Query query2 = sessionFactory.getCurrentSession().createQuery(hql);
			query2.executeUpdate();
		} catch (Exception e) {
			throw e;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<TrollPhoto> getTrollPhotoByUploader(int uploaderId, int pageNumber) throws Exception {
		try {
			String hql = "FROM TrollPhoto t WHERE t.status = 'A' AND t.uploader.id = :id ORDER BY t.id DESC";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setInteger("id", uploaderId);
			query.setFirstResult((pageNumber - 1) * Constant.PHOTO_PAGE_SIZE);
			query.setMaxResults(Constant.PHOTO_PAGE_SIZE);
			return query.list();
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public TrollJoke getTrollJokeById(int jokeId) throws Exception {
		TrollJoke troll = null;
		try {
			troll = (TrollJoke) sessionFactory.getCurrentSession().get(TrollJoke.class, jokeId);
			if (troll != null) {
				updateViewsNumber(jokeId);
			}
		} catch (Exception e) {
			throw e;
		}
		return troll;
	}

	@Override
	public String getMessage() throws Exception {
		try {
			String sql = "SELECT m.Msg FROM MESSAGE m WHERE m.Active = 1";
			SQLQuery sqlQuery = sessionFactory.getCurrentSession().createSQLQuery(sql)
					.addScalar("Msg", Hibernate.STRING);
			String msg = (String) sqlQuery.uniqueResult();
			return msg;
		} catch (Exception e) {
			throw e;
		}
	}

}
